#!/bin/sh
#抽取日表数据到ads_trip_engine_data_engine_type_statistics_pdi表中
CUR_DIR=$(cd "$(dirname "${BASH_SOURCE-$0}")"; pwd)
UP_DIR=$(dirname $(dirname $(dirname "$PWD")))
echo ${UP_DIR}
source ${UP_DIR}/system_env.sh


START_TIME=$(date +%s)
hive_db=${hive_db}

if [ -z "${hive_db}" ];then
  hive_db=dataplatform_trip
else
  hive_db=${hive_db}
fi


if [ -z "$1" ];then
  part_time=$(date -d "1 days ago" +%Y%m%d)
else
  part_time=$1
fi

sql_suffix=$(cat <<EOF
  insert overwrite table ${hive_db}.ads_trip_engine_data_engine_type_statistics_pdi partition (day=${part_time})
  select
    '' as vin,
    a.engine_type as engine_type,
    avg(a.speed_avg) as speed_avg,
    avg(a.barometric_pressure_avg) as barometric_pressure_avg,
    avg(a.output_torque_avg) as output_torque_avg,
    avg(a.friction_torque_avg) as friction_torque_avg,
    avg(a.rotation_avg) as rotation_avg,
    avg(a.fuel_consumption_avg) as fuel_consumption_avg,
    avg(a.scr_up_nox_avg) as scr_up_nox_avg,
    avg(a.scr_down_nox_avg) as scr_down_nox_avg,
    avg(a.reagent_allowance_avg) as reagent_allowance_avg,
    avg(a.air_inflow_avg) as air_inflow_avg,
    avg(a.scr_in_avg) as scr_in_avg,
    avg(a.scr_out_avg) as scr_out_avg,
    avg(a.dropout_voltage_avg) as dropout_voltage_avg,
    avg(a.coolant_temperature_avg) as coolant_temperature_avg,
    avg(a.tank_level_avg) as tank_level_avg,
    avg(a.ternary_down_nox_output_avg) as ternary_down_nox_output_avg,
    avg(a.ternary_up_o2_output_avg) as ternary_up_o2_output_avg,
    avg(a.ternary_down_o2_output_avg) as ternary_down_o2_output_avg,
    max(a.speed_max) as speed_max,
    max(a.barometric_pressure_max) as barometric_pressure_max,
    max(a.output_torque_max) as output_torque_max,
    max(a.friction_torque_max) as friction_torque_max,
    max(a.rotation_max) as rotation_max,
    max(a.fuel_consumption_max) as fuel_consumption_max,
    max(a.scr_up_nox_max) as scr_up_nox_max,
    max(a.scr_down_nox_max) as scr_down_nox_max,
    max(a.reagent_allowance_max) as reagent_allowance_max,
    max(a.air_inflow_max) as air_inflow_max,
    max(a.scr_in_max) as scr_in_max,
    max(a.scr_out_max) as scr_out_max,
    max(a.dropout_voltage_max) as dropout_voltage_max,
    max(a.coolant_temperature_max) as coolant_temperature_max,
    max(a.tank_level_max) as tank_level_max,
    max(a.ternary_down_nox_output_max) as ternary_down_nox_output_max,
    max(a.ternary_up_o2_output_max) as ternary_up_o2_output_max,
    max(a.ternary_down_o2_output_max) as ternary_down_o2_output_max,
    min(a.speed_min) as speed_min,
    min(a.barometric_pressure_min) as barometric_pressure_min,
    min(a.output_torque_min) as output_torque_min,
    min(a.friction_torque_min) as friction_torque_min,
    min(a.rotation_min) as rotation_min,
    min(a.fuel_consumption_min) as fuel_consumption_min,
    min(a.scr_up_nox_min) as scr_up_nox_min,
    min(a.scr_down_nox_min) as scr_down_nox_min,
    min(a.reagent_allowance_min) as reagent_allowance_min,
    min(a.air_inflow_min) as air_inflow_min,
    min(a.scr_in_min) as scr_in_min,
    min(a.scr_out_min) as scr_out_min,
    min(a.dropout_voltage_min) as dropout_voltage_min,
    min(a.coolant_temperature_min) as coolant_temperature_min,
    min(a.tank_level_min) as tank_level_min,
    min(a.ternary_down_nox_output_min) as ternary_down_nox_output_min,
    min(a.ternary_up_o2_output_min) as ternary_up_o2_output_min,
    min(a.ternary_down_o2_output_min) as ternary_down_o2_output_min,
    min(a.ternary_temperature_output_min) as ternary_temperature_output_min,
    max(a.ternary_temperature_output_max) as ternary_temperature_output_max,
    avg(a.ternary_temperature_output_avg) as ternary_temperature_output_avg
  from
  (select
    vin,
    engine_type,
    avg(speed_avg) as speed_avg,
    avg(barometric_pressure_avg) as barometric_pressure_avg,
    avg(output_torque_avg) as output_torque_avg,
    avg(friction_torque_avg) as friction_torque_avg,
    avg(rotation_avg) as rotation_avg,
    avg(fuel_consumption_avg) as fuel_consumption_avg,
    avg(scr_up_nox_avg) as scr_up_nox_avg,
    avg(scr_down_nox_avg) as scr_down_nox_avg,
    avg(reagent_allowance_avg) as reagent_allowance_avg,
    avg(air_inflow_avg) as air_inflow_avg,
    avg(scr_in_avg) as scr_in_avg,
    avg(scr_out_avg) as scr_out_avg,
    avg(dropout_voltage_avg) as dropout_voltage_avg,
    avg(coolant_temperature_avg) as coolant_temperature_avg,
    avg(tank_level_avg) as tank_level_avg,
    avg(ternary_down_nox_output_avg) as ternary_down_nox_output_avg,
    avg(ternary_up_o2_output_avg) as ternary_up_o2_output_avg,
    avg(ternary_down_o2_output_avg) as ternary_down_o2_output_avg,
    avg(ternary_temperature_output_avg) as ternary_temperature_output_avg,
    max(speed_max) as speed_max,
    max(barometric_pressure_max) as barometric_pressure_max,
    max(output_torque_max) as output_torque_max,
    max(friction_torque_max) as friction_torque_max,
    max(rotation_max) as rotation_max,
    max(fuel_consumption_max) as fuel_consumption_max,
    max(scr_up_nox_max) as scr_up_nox_max,
    max(scr_down_nox_max) as scr_down_nox_max,
    max(reagent_allowance_max) as reagent_allowance_max,
    max(air_inflow_max) as air_inflow_max,
    max(scr_in_max) as scr_in_max,
    max(scr_out_max) as scr_out_max,
    max(dropout_voltage_max) as dropout_voltage_max,
    max(coolant_temperature_max) as coolant_temperature_max,
    max(tank_level_max) as tank_level_max,
    max(ternary_down_nox_output_max) as ternary_down_nox_output_max,
    max(ternary_up_o2_output_max) as ternary_up_o2_output_max,
    max(ternary_down_o2_output_max) as ternary_down_o2_output_max,
    max(ternary_temperature_output_max) as ternary_temperature_output_max,
    min(speed_min) as speed_min,
    min(barometric_pressure_min) as barometric_pressure_min,
    min(output_torque_min) as output_torque_min,
    min(friction_torque_min) as friction_torque_min,
    min(rotation_min) as rotation_min,
    min(fuel_consumption_min) as fuel_consumption_min,
    min(scr_up_nox_min) as scr_up_nox_min,
    min(scr_down_nox_min) as scr_down_nox_min,
    min(reagent_allowance_min) as reagent_allowance_min,
    min(air_inflow_min) as air_inflow_min,
    min(scr_in_min) as scr_in_min,
    min(scr_out_min) as scr_out_min,
    min(dropout_voltage_min) as dropout_voltage_min,
    min(coolant_temperature_min) as coolant_temperature_min,
    min(tank_level_min) as tank_level_min,
    min(ternary_down_nox_output_min) as ternary_down_nox_output_min,
    min(ternary_up_o2_output_min) as ternary_up_o2_output_min,
    min(ternary_down_o2_output_min) as ternary_down_o2_output_min,
    min(ternary_temperature_output_min) as ternary_temperature_output_min
  from ${hive_db}.ads_trip_engine_data_statistics_pdi
  where day = ${part_time}
  group by vin, engine_type) a group by a.engine_type;
EOF
)

CUR_DIR=$(cd "$(dirname "$0")"; pwd)
hive_config=$(cat  "${CUR_DIR}"/hive.config)
sql="${hive_config}${sql_suffix}"
echo "导入ads_trip_engine_data_engine_type_statistics_pdi的SQL为：${sql}"

#执行语句·
hive --hiveconf "tez.queue.name=${queue_name}" -e "${sql}"
if [ $? -ne 0 ];then
  echo "导入ads_trip_engine_data_engine_type_statistics_pdi失败，耗时$((`date +%s`-START_TIME))秒..."
else
  echo "导入ads_trip_engine_data_engine_type_statistics_pdi成功，耗时$((`date +%s`-START_TIME))秒..."
fi